-- Get File details for an attachment
CREATE  view vAttachmentDetails as
select AnnotationId as AttachmentId, FileName, FileSize, MimeType, u.FullName as OwnerName, a.CreatedOn, a.ModifiedOn, cast(0 as bit) as EmailAttachment, cast(null as uniqueidentifier) as EmailID 
from AnnotationBase a join systemuserbase u on a.OwningUser = u.systemuserid
where FileName is not null
union
select ActivityMimeAttachmentId, FileName, FileSize, MimeType, u.FullName, a.CreatedOn, a.ModifiedOn, cast(1 as bit), ama.ActivityID 
from ActivityMimeAttachment ama
join ActivityPointerBase a on ama.ActivityId = a.activityid
join systemuserbase u on a.OwningUser = u.systemuserid
where FileName is not null

GO

-- Get
CREATE  view vAttachmentObject
as
-- Get directly attached files
select AnnotationId as AttachmentId, ObjectId, ObjectTypeCode, cast(0 as bit) as EmailAttachment 
from annotationbase
where filename is not null
and ObjectId is not null
union
-- Get files attached to activities
select AnnotationId as AttachmentId, PartyId, PartyObjectTypeCode, cast(0 as bit) as EmailAttachment 
from annotationbase ab join activityparty ap on ab.objectid = ap.activityid
where filename is not null 
and ObjectId is not null
and ObjectTypeCode between 4200 and 4220	-- Activity types
and PartyId is not null
and PartyObjectTypeCode != 8		-- Ignore Users
union
-- Get email attachments
select ActivityMimeAttachmentId, PartyId, PartyObjectTypeCode, cast(1 as bit)
from ActivityMimeAttachment ama
join activityparty ap on ama.activityid = ap.activityid
where fileName is not null
and PartyId is not null
and PartyObjectTypeCode != 8		-- Ignore Users

GO

CREATE  view vAttachmentByAccount
as
-- Direct attachment to the account
select a.name as AccountName, a.accountid as AccountID, a.name as ObjectName, a.accountid as ObjectID, cast('Account' as nvarchar(128)) as TypeName, ao.ObjectTypeCode as OTC, ao.AttachmentId, ao.EmailAttachment
from accountbase a
join vAttachmentObject ao on a.accountid = ao.ObjectID and ao.ObjectTypeCode = 1
-- Attached to any contacts
union
select a.name, a.accountid, c.fullname, c.contactid, 'Contact', ao.ObjectTypeCode, ao.AttachmentId, ao.EmailAttachment
from accountbase a
join contactbase c on a.accountid = c.accountid
join vAttachmentObject ao on c.contactid = ao.ObjectID and ao.ObjectTypeCode = 2
-- Attached to any opportunities
union
select a.name, a.accountid, o.name, o.opportunityid, 'Opportunity', ao.ObjectTypeCode, ao.AttachmentId, ao.EmailAttachment
from accountbase a
join opportunitybase o on a.accountid = o.accountid
join vAttachmentObject ao on o.opportunityid = ao.ObjectID and ao.ObjectTypeCode = 3
-- Attached to any quotes
union
select a.name, a.accountid, q.name, q.quoteid, 'Quote', ao.ObjectTypeCode, ao.AttachmentId, ao.EmailAttachment
from accountbase a
join quotebase q on a.accountid = q.accountid
join vAttachmentObject ao on q.quoteid = ao.ObjectID and ao.ObjectTypeCode = 1084
-- Attached to any quotes
union
select a.name, a.accountid, so.name, so.salesorderid, 'Order', ao.ObjectTypeCode, ao.AttachmentId, ao.EmailAttachment
from accountbase a
join salesorderbase so on a.accountid = so.accountid
join vAttachmentObject ao on so.salesorderid = ao.ObjectID and ao.ObjectTypeCode = 1088
-- Attached to any cases
union
select a.name, a.accountid, i.title, i.incidentid, 'Case', ao.ObjectTypeCode, ao.AttachmentId, ao.EmailAttachment
from accountbase a
join incidentbase i on a.accountid = i.accountid
join vAttachmentObject ao on i.incidentid = ao.ObjectID and ao.ObjectTypeCode = 112

GO 

CREATE   view vAttachmentByAccountFull as
select v.AccountName, v.AccountID, v.ObjectID, v.ObjectName, v.TypeName, cast (v.OTC as nvarchar(10)) as OTC, a.*
, FileSize / 1024 as FileSizeKB
, case when charindex('.', FileName) > 0 then substring(FileName, charindex('.', FileName) + 1, datalength(FileName) - charindex('.', FileName) - 1) end as FileExtension
from FilteredAccount fa			-- Use Filtered view here to enforce security
join vAttachmentByAccount v on fa.accountid = v.accountid
join vAttachmentDetails a on v.AttachmentId = a.AttachmentId
and v.EmailAttachment = a.EmailAttachment

GO

-- Grant permission to ReportingGroup
declare @sql nvarchar(2000), @groupName nvarchar(128)
select @groupName = ReportingGroupName from filteredorganization
set @sql = 'GRANT SELECT ON vAttachmentByAccountFull TO [' + @groupName + ']'
exec (@sql)

GO

